﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using OnlineCellphoneStore.Models;
using System.Text;

namespace OnlineCellphoneStore
{
    public static class SQLAdapter
    {
        // User 
        public static int VerifyUser(string username, string password)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.VerifyUser(password, username);
                if (dt.Rows.Count == 0)
                    return -1;
                else
                    return dt[0].ClientID;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static int CheckIfUserNameExists(string username)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.CheckIfUsernameExists(username);
                if (dt.Rows.Count == 0)
                    return -1;
                else
                    return dt[0].ClientID;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static int CheckIfMailExists(string email)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.CheckIfMailExists(email);
                if (dt.Rows.Count == 0)
                    return -1;
                else
                    return dt[0].ClientID;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static string InsertUser(User user)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                int success = adapter.InsertUser(user.m_sUsername, user.m_sFirstname, user.m_sLastname, user.m_sAddress, user.m_sCity, user.m_sCountry, user.m_sPhone, user.m_sCellular, user.m_sMail, user.m_sCreditCard, user.m_iIsAdmin, user.m_sPassword, user.m_sSQ, user.m_sAns, user.m_dVisit);
                if (success == 1)
                {
                    DAL.DBAdapter.ClientsDataTable dt = adapter.VerifyUser(user.m_sPassword, user.m_sUsername);
                    // Gets his client ID
                    int clientID = dt[0].ClientID;
                    DAL.DBAdapterTableAdapters.ClientCategoryTableAdapter catAdapter = new DAL.DBAdapterTableAdapters.ClientCategoryTableAdapter();
                    // Insert into client categories
                    foreach (int item in user.m_lCategories)
                    {
                        catAdapter.InsertClientCategories(clientID, item);
                    }
                    return clientID.ToString();
                }
                else
                {
                    return "";
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static List<string> GetSecurityQuestionAnswer(string username)
        {
            try
            {
                List<string> l = new List<string>();
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.GetUserForgotPasswordDetails(username);
                string question = dt[0].SecurityQuestion;
                string answer = dt[0].SecurityAnswer;
                l.Add(question);
                l.Add(answer);
                return l;
            }
            catch (Exception)
            {

                throw;
            }
        }
        public static string GetPassword(string username)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.CheckIfUsernameExists(username);
                return dt[0].Password;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static List<string> GetUserNameAndLastVisit(int id)
        {
            try
            {
                List<string> l = new List<string>();
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                DAL.DBAdapter.ClientsDataTable dt = adapter.GetUser(id);
                string username = dt[0].UserName;
                string visit = dt[0].LastEntered.ToString();
                l.Add(username);
                l.Add(visit);
                return l;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static void UpdateLastVisit(int id, DateTime now)
        {
            try
            {
                DAL.DBAdapterTableAdapters.ClientsTableAdapter adapter = new DAL.DBAdapterTableAdapters.ClientsTableAdapter();
                adapter.UpdateLastVisit(now, id);
                return;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Products
        public static DAL.DBAdapter.CellphonesDataTable GetLatestProductsAdded()
        {
            try
            {
                DAL.DBAdapterTableAdapters.CellphonesTableAdapter adapter = new DAL.DBAdapterTableAdapters.CellphonesTableAdapter();
                DAL.DBAdapter.CellphonesDataTable dt = adapter.GetLatestAddedCellphones(DateTime.Now.AddDays(-7));
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static DAL.DBAdapter.CellphoneDisplayDataTable GetPhoneInfo(int id)
        {
            try
            {
                DAL.DBAdapterTableAdapters.CellphoneDisplayTableAdapter adapter = new DAL.DBAdapterTableAdapters.CellphoneDisplayTableAdapter();
                DAL.DBAdapter.CellphoneDisplayDataTable dr = adapter.GetCellphoneDisplayData(id);
                return dr;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static DAL.DBAdapter.FavoriteCellphonesDataTable GetFavoriteCellphones(int id)
        {
            try
            {
                DAL.DBAdapterTableAdapters.FavoriteCellphonesTableAdapter adapter = new DAL.DBAdapterTableAdapters.FavoriteCellphonesTableAdapter();
                DAL.DBAdapter.FavoriteCellphonesDataTable dt = adapter.GetFavoriteUserCellphones(id);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Orders
        public static DAL.DBAdapter.OrdersDataTable GetAllClientOrders(int id)
        {
            try
            {
                DAL.DBAdapterTableAdapters.OrdersTableAdapter adapter = new DAL.DBAdapterTableAdapters.OrdersTableAdapter();
                DAL.DBAdapter.OrdersDataTable dt = adapter.GetAllClientOrders(id);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static DAL.DBAdapter.CellphoneInOrderDataTable GetCellphonesInOrder(int id)
        {
            try
            {
                DAL.DBAdapterTableAdapters.CellphoneInOrderTableAdapter adapter = new DAL.DBAdapterTableAdapters.CellphoneInOrderTableAdapter();
                DAL.DBAdapter.CellphoneInOrderDataTable dt = adapter.GetSpecificOrder(id);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static int InsertOrder(Order order)
        {
            try
            {
                DAL.DBAdapterTableAdapters.OrdersTableAdapter adapter1 = new DAL.DBAdapterTableAdapters.OrdersTableAdapter();
                DAL.DBAdapterTableAdapters.CellphonesInOrdersTableAdapter adapter2 = new DAL.DBAdapterTableAdapters.CellphonesInOrdersTableAdapter();
                DAL.DBAdapterTableAdapters.CellphonesTableAdapter adapter3 = new DAL.DBAdapterTableAdapters.CellphonesTableAdapter();
                adapter1.InsertOrder(order.ClientID, order.OrderDate, order.ShipingDate, order.Currency, order.TotalAmount.ToString());
                DAL.DBAdapter.OrdersDataTable dt = adapter1.GetOrderId(order.ClientID, order.OrderDate);
                int OrderId = dt[0].OrderID;
                foreach (KeyValuePair<int, int> cellphone in order.CellphonesToUnitsPurcahse)
                {
                    adapter2.InsertCellphoneToOrder(OrderId, cellphone.Key, cellphone.Value);
                }
                foreach (KeyValuePair<int, int> cellphone in order.CellphonesToUpdatedUnits)
                {
                    adapter3.UpdateStock(cellphone.Value, cellphone.Key);
                }
                return OrderId;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Cart
        public static DAL.DBAdapter.CartCellphoneDataTable InsertCellphoneToCart(int id)
        {
            try
            {
                DAL.DBAdapterTableAdapters.CartCellphoneTableAdapter adapter = new DAL.DBAdapterTableAdapters.CartCellphoneTableAdapter();
                DAL.DBAdapter.CartCellphoneDataTable dt = adapter.GetCellphoneToCart(id);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Search
        public static DAL.DBAdapter.CellphonesWithManuDataTable GetAllCellphones()
        {
            try
            {
                DAL.DBAdapterTableAdapters.CellphonesWithManuTableAdapter adapter = new DAL.DBAdapterTableAdapters.CellphonesWithManuTableAdapter();
                DAL.DBAdapter.CellphonesWithManuDataTable dt = adapter.GetAllCellphonesWithManu();
                return dt;
            }
            catch (Exception)
            {
                throw;;
            }
        }

        // TreeView
        public static DAL.DBAdapter.TreeViewCellphonesDataTable GetTreeData()
        {
            try
            {
                DAL.DBAdapterTableAdapters.TreeViewCellphonesTableAdapter adapter = new DAL.DBAdapterTableAdapters.TreeViewCellphonesTableAdapter();
                DAL.DBAdapter.TreeViewCellphonesDataTable dt = adapter.GetTreeViewCellphones();
                return dt;
            }
            catch (Exception)
            {
                throw;
            }

        }
    }
}